USE [HaOnAzure]
GO

/****** Object:  StoredProcedure [HAOnAzure].[ShowDatabasesOnHA]    Script Date: 12/27/2013 2:44:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [HAOnAzure].[ShowDatabasesOnHA] AS
SET NOCOUNT ON;

DECLARE @AccountName NVARCHAR(255);
DECLARE @SharedKey NVARCHAR(4000);

SELECT TOP 1 @AccountName = AccountName, @SharedKey = SharedKey 
FROM [HAOnAzure].[Credential] 
WHERE IsMaster = 1;

IF @AccountName IS NULL OR @SharedKey IS NULL
	THROW 53000, 'Default credential not found. Add it and try again.', 1;

SELECT 
	Attributes.value('(//AccountName)[1]', 'nvarchar(260)') AS AccountName
	, Attributes.value('(//DatabaseName)[1]', 'nvarchar(260)') AS DatabaseName
	, Attributes.value('(//name)[1]', 'sysname') AS [name]
	, Attributes.value('(//type)[1]', 'tinyint') AS [type]
	, Attributes.value('(//physical_name)[1]', 'nvarchar(260)') AS physical_name
	FROM [SQL2AzureCLR].[Azure].QueryTable(
	@AccountName, @SharedKey, 1,
	'HaOnAzureDatabase', NULL);
GO


